MyBatis 动态 SQL

概述

因为传递的参数可能不全，那这种时候如何动态的变化 SQL 呢？

例如

<select id="getUser"  parameterType="com.alsritter.pojo.User" resultType="com.alsritter.pojo.User">
    select * from users where id = #{id} and username = #{username} and password = #{password}
</select>

然后根据上面那个 SQL 语句传入对应的参数，如下

 @Test
 public void getUser() {
     try (SqlSession sqlSession = MyBatisUtils.getSqlSession()) {
         User user = new User();
         user.setId(1);
         user.setUsername("张三");
         user.Password("123");
         
         // 执行SQl
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);

         // 把上面那个 user 当作参数传进去
         System.out.println(mapper.getUser(user));
     }
 }

但是有个问题，万一 User 对象里面有些参数为空呢？

User user = new User();
// 像这样只传入一个参数
user.setUsername("张三");

那就不能再直接使用那个 SQL 语句了（只传入一个参数其他的为空）

select * from users where id = #{id} and username = #{username} and password = #{password}

默认是把全部参数都丢到里面去查询，而当少了几个条件就无法再使用了

参考资料 官方文档 动态 SQL

所以在 JDBC 时一般需要手动拼接 SQL 但是到了 MyBatis 可以使用其提供的一些标签来进行动态参数

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

if 语句

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

可以多次 if

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

“switch” 语句

MyBatis 提供的 choose、when、otherwise 标签用法有点像 Switch 语句

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

动态 Where 关键字

因为前面的 SQL 本质还是动态拼接，所以当 if 的条件都不满足时可能会出现下面这种情况导致查询出错

-- 只有一个 where 在那里，后面没有条件
SELECT * FROM BLOG
WHERE

所以可以使用 MyBatis 的 where 标签，只有某个条件满足时才把 where 拼接到 SQL 语句上

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

trim 动态修改

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

上面的这个还有一种情况可能会导致错误发生，就算第一个 if 不满足，第二个 if 满足

那就有可能会导致 SELECT * FROM BLOG WHERE AND title like #{title} 这样的语句出现，因为 AND 前面没有判断条件，所以会报错，这时就可以使用 trim 标签动态匹配 AND 或者 OR 关键字，如果不满足条件就把这个 AND 或者 OR 去掉

prefix： 在 trim 标签内 sql 语句加上前缀。

suffix：在 trim 标签内sql语句加上后缀。

suffixOverrides：指定去除多余的后缀内容，如：suffixOverrides=","，去除 trim 标签内 sql 语句多余的后缀","。

prefixOverrides：指定去除多余的前缀内容

<trim prefix="WHERE" prefixOverrides="AND |OR ">
<!-- prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的） -->
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
</trim>

除了匹配 OR 和 AND 还可以用来处理结尾逗号之类的问题，或者自动加 ( )

模板

<insert id="insertSelective" parameterType="com.bootdo.system.domain.LogDO">
    insert into sys_log
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="userId != null">
        user_id,
      </if>
      <if test="username != null">
        username,
      </if>
      <if test="operation != null">
        operation,
      </if>
      <if test="time != null">
        time,
      </if>
      <if test="method != null">
        method,
      </if>
      <if test="params != null">
        params,
      </if>
      <if test="ip != null">
        ip,
      </if>
      <if test="gmtCreate != null">
        gmt_create,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
      <if test="userId != null">
        #{userId,jdbcType=BIGINT},
      </if>
      <if test="username != null">
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="operation != null">
        #{operation,jdbcType=VARCHAR},
      </if>
      <if test="time != null">
        #{time,jdbcType=INTEGER},
      </if>
      <if test="method != null">
        #{method,jdbcType=VARCHAR},
      </if>
      <if test="params != null">
        #{params,jdbcType=VARCHAR},
      </if>
      <if test="ip != null">
        #{ip,jdbcType=VARCHAR},
      </if>
      <if test="gmtCreate != null">
        #{gmtCreate,jdbcType=TIMESTAMP},
      </if>
    </trim>
</insert>

动态 Set 语句

就同上面的 where 一样，Set 语句也存在那样的问题，所以也可以使用 set 标签来修改数据

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

遍历数组

当需要对传入的参数进行遍历时也可以使用这个 foreach 标签

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。

当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。

当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

使用注解

建议不要用，因为会导致注解的内容极其恶心（就像 Vue 的 Template 一样）

@Update({"<script>",
  "update Author",
  "  <set>",
  "    <if test='username != null'>username=#{username},</if>",
  "    <if test='password != null'>password=#{password},</if>",
  "    <if test='email != null'>email=#{email},</if>",
  "    <if test='bio != null'>bio=#{bio}</if>",
  "  </set>",
  "where id=#{id}",
  "</script>"})
void updateAuthorValues(Author author);